SQL Server default account guest must be removed from each database.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-41400	SQL2-00-023800	SV-53926r1_rule		Medium

Description
SQL Server's default 'guest' account and any user-defined 'public' account(s) may be assigned privileges that could give data access to an attacker. Well-known SQL Server default accounts would likely be targeted by attackers and thus more prone to providing unauthorized access to SQL Server data. If SQL Server's default accounts and user-defined 'guest' account are not removed or set to having zero privileges an attacker would be more likely to gain access to SQL Server data. The default 'guest' account posses a compromise to user-defined database data that could compromise the viewing of SQL Server system data and in some cases configuration settings. This SQL Server's default 'guest' account must have its privileges set to near zero, except for the ownership of the guest schema to minimize any chance of unauthorized access.

Description

SQL Server's default 'guest' account and any user-defined 'public' account(s) may be assigned privileges that could give data access to an attacker. Well-known SQL Server default accounts would likely be targeted by attackers and thus more prone to providing unauthorized access to SQL Server data. If SQL Server's default accounts and user-defined 'guest' account are not removed or set to having zero privileges an attacker would be more likely to gain access to SQL Server data. The default 'guest' account posses a compromise to user-defined database data that could compromise the viewing of SQL Server system data and in some cases configuration settings. This SQL Server's default 'guest' account must have its privileges set to near zero, except for the ownership of the guest schema to minimize any chance of unauthorized access.

STIG	Date
Microsoft SQL Server 2012 Database Security Technical Implementation Guide	2014-06-23

Details

Check Text ( C-47938r2_chk )
Obtain list of 'guest' accounts, if any exist, from system documentation. Check SQL Server for the existence of 'guest' accounts in every user-defined database instance by running the following script: EXEC sp_MSforeachdb ' IF NOT ''?'' IN (''master'', ''tempdb'', ''model'', ''msdb'') BEGIN USE ? SELECT ''?'' AS ''Database'' , su.name AS ''db Account Name'' , sp.name AS ''SQL Server Account Name'' FROM sys.sysusers su LEFT JOIN sys.server_principals sp ON su.sid = sp.sid WHERE ( su.name like ''gues%'' OR sp.name like ''gues%'' ) AND NOT su.sid = CONVERT(VARBINARY(85), 0x00) END ' If any 'guest' accounts exist and SQL Server is not setup for public information access, this is a finding.

Check Text ( C-47938r2_chk )

Obtain list of 'guest' accounts, if any exist, from system documentation.

Check SQL Server for the existence of 'guest' accounts in every user-defined database instance by running the following script:

EXEC sp_MSforeachdb '
IF NOT ''?'' IN (''master'', ''tempdb'', ''model'', ''msdb'')
BEGIN
USE ?
SELECT ''?'' AS ''Database''
, su.name AS ''db Account Name''
, sp.name AS ''SQL Server Account Name''
FROM sys.sysusers su
LEFT JOIN sys.server_principals sp
ON su.sid = sp.sid
WHERE ( su.name like ''gues%''
OR sp.name like ''gues%'' )
AND NOT su.sid = CONVERT(VARBINARY(85), 0x00)
END '

If any 'guest' accounts exist and SQL Server is not setup for public information access, this is a finding.

Fix Text (F-46826r2_fix)
USE <'database name'> DROP USER <'guest account name'> -- Removes user from database DROP LOGIN <'guest account name'> -- Removes user from system Note: Removal of SQL Server 'guest' account privileges can adversely affect system operation. Testing must be done to insure that removal of privileges does not adversely affect successful system operations.